package org.posupe.candidatos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.posupe.bdr.PersistenceMechanismException;
import org.posupe.bdr.PersistenceMechanismRDBMS;
import org.posupe.pessoas.PessoaFisicaInexistenteException;
import org.posupe.pessoas.RepositorioPessoasFisicasBDR;
import org.posupe.util.RepositorioException;

public class RepositorioCandidatosBDR implements RepositorioCandidatos {

	private static final String SQL_SELECT = "SELECT * FROM CANDIDATO WHERE CODIGO = ?";

	private static final String SQL_INSERT = "INSERT INTO CANDIDATO (CODIGO, PRETENSAO_SALARIAL) VALUES (?, ?)";

	private static final String SQL_UPDATE = "UPDATE CANDIDATO SET PRETENSAO_SALARIAL = ? WHERE CODIGO = ?";

	private static final String SQL_DELETE = "DELETE FROM CANDIDATO WHERE CODIGO = ?";

	private static final String SQL_TODOS = "SELECT * FROM CANDIDATO";

	private static RepositorioCandidatosBDR instance;

	private PersistenceMechanismRDBMS pm;

	private RepositorioCandidatosBDR() throws RepositorioException {
		try {
			this.pm = PersistenceMechanismRDBMS.getInstance();
			this.pm.connect();
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		}
	}

	public static RepositorioCandidatosBDR getInstance()
			throws RepositorioException {
		if (instance == null) {
			instance = new RepositorioCandidatosBDR();
		}
		return instance;
	}

	public void atualizar(Candidato candidato)
			throws CandidatoInexistenteException, RepositorioException {
		try {
			PreparedStatement stmt = getStatement(SQL_UPDATE);

			try {
				RepositorioPessoasFisicasBDR.getInstance().atualizar(candidato);

				stmt.setDouble(1, candidato.getPretencaoSalarial());
				stmt.setString(2, candidato.getCodigo());
				int i = stmt.executeUpdate();
				if (i == 0) {
					throw new CandidatoInexistenteException(candidato
							.getCodigo());
				}
			} finally {
				stmt.close();
			}
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} catch (PessoaFisicaInexistenteException e) {
			throw new CandidatoInexistenteException(candidato.getCodigo());
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}

	}

	public boolean existe(String cpf) throws RepositorioException {
		boolean retorno = false;
		PreparedStatement stmt = getStatement(SQL_SELECT);
		ResultSet rs = null;

		try {
			try {
				stmt.setString(1, cpf);
				rs = stmt.executeQuery();
				if (rs.next()) {
					retorno = true;
				}
			} finally {
				if (rs != null)
					rs.close();
			}
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return retorno;
	}

	public IteratorCandidatos getIterator() throws RepositorioException {
		IteratorCandidatosArrayList retorno = new IteratorCandidatosArrayList();
		ResultSet rs = null;
		Candidato candidato = null;

		try {
			PreparedStatement stmt = getStatement(SQL_TODOS);

			try {
				rs = stmt.executeQuery();

				while (rs.next()) {
					candidato = new Candidato(rs.getString("CODIGO"));
					candidato.setPretencaoSalarial(rs
							.getDouble("PRETENSAO_SALARIAL"));
					try {
						RepositorioPessoasFisicasBDR.getInstance().procurar(
								candidato);
					} catch (PessoaFisicaInexistenteException e) {
					}
					retorno.add(candidato);
				}
			} finally {
				stmt.close();
				rs.close();
			}

		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}

		return retorno;
	}

	public void inserir(Candidato candidato)
			throws CandidatoJaCadastradoException, RepositorioException {
		PreparedStatement stmt = getStatement(SQL_INSERT);

		try {

			try {
				RepositorioPessoasFisicasBDR.getInstance().inserir(candidato);
				stmt.setString(1, candidato.getCodigo());
				stmt.setDouble(2, candidato.getPretencaoSalarial());
				stmt.executeUpdate();
			} finally {
				stmt.close();
			}
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}

	}

	public Candidato procurar(String cpf) throws CandidatoInexistenteException,
			RepositorioException {
		Candidato retorno = null;
		PreparedStatement stmt = getStatement(SQL_SELECT);
		ResultSet rs = null;
		try {
			try {
				// Procurando os dados do Candidato
				stmt.setString(1, cpf);
				rs = stmt.executeQuery();

				if (rs.next()) {
					retorno = new Candidato(rs.getString("CODIGO"));
					try {
						RepositorioPessoasFisicasBDR.getInstance().procurar(
								retorno);
					} catch (PessoaFisicaInexistenteException e) {
						throw new CandidatoInexistenteException(retorno
								.getCodigo());
					}
				} else {
					throw new CandidatoInexistenteException(cpf);
				}
			} finally {
				stmt.close();
				rs.close();
			}

		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
		return retorno;
	}

	public IteratorCandidatos procurar(Candidato candidato)
			throws RepositorioException {
		IteratorCandidatosArrayList retorno = new IteratorCandidatosArrayList();
		IteratorCandidatos it = this.getIterator();
		Candidato c = null;

		while (it.hasNext()) {
			c = it.next();
			if (c.match(candidato)) {
				retorno.add(c);
			}
		}
		return retorno;
	}

	public void remover(String cpf) throws CandidatoInexistenteException,
			RepositorioException {
		try {
			PreparedStatement stmt = getStatement(SQL_DELETE);

			try {
				stmt.setString(1, cpf);
				int i = stmt.executeUpdate();
				if (i == 0) {
					throw new CandidatoInexistenteException(cpf);
				}
			} finally {
				stmt.close();
			}

			RepositorioPessoasFisicasBDR.getInstance().remover(cpf);
		} catch (PessoaFisicaInexistenteException e) {
			throw new CandidatoInexistenteException(cpf);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}

	}

	private PreparedStatement getStatement(String statement)
			throws RepositorioException {
		try {
			return (PreparedStatement) this.pm
					.getCommunicationChannel(statement);
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		}
	}
}
